﻿-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[GetProjectRelatedData]
	@PropertyId uniqueidentifier
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	DECLARE @RoadId uniqueidentifier,
			@LocationId uniqueidentifier,
			@StateId uniqueidentifier,
			@CountryId uniqueidentifier,
			@ProjectId uniqueidentifier
			

	
	
	SELECT @ProjectId = ProjectId FROM AW_Property WHERE PropertyId = @PropertyId AND StatusDelete = 0
	--GET Property
	SELECT * , ar.LocationId, al.StateId FROM AW_Property ap
	JOIN  AW_Road ar on ar.RoadId = ap.RoadId
	JOIN AW_Location al on al.LocationId = ar.LocationId
	JOIN AW_State st on st.StateId = al.StateId WHERE PropertyId = @PropertyId
	

	SELECT @RoadId=RoadId FROM AW_Project WHERE ProjectId =@ProjectId
    -- Project List
	SELECT Project, ProjectId FROM AW_Project WHERE RoadId =@RoadId AND StatusDelete = 0
	

	SELECT @LocationId = LocationId FROM AW_Road WHERE RoadId =@RoadId
	-- Road List
	SELECT Road, RoadId FROM AW_Road WHERE LocationId =@LocationId AND StatusDelete = 0
	
	SELECT @StateId = StateId FROM AW_Location WHERE LocationId =@LocationId
	-- Location List
	SELECT Location, LocationId FROM AW_Location WHERE @StateId = StateId AND StatusDelete = 0
	
	SELECT @CountryId = countryId FROM AW_State WHERE @StateId = StateId
	SELECT State, StateId FROM AW_State WHERE @CountryId = countryId AND StatusDelete = 0
	
END